﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Xml;
using System.Windows.Forms;
using System.IO;
using System.Data.SQLite;
using System.Globalization;


namespace pistolety3
{
    class pistolety
    {
        public DataSet dokumenty(string query, OdbcConnection con)
        {
            DataSet ds = new DataSet();
            try
            {
                OdbcDataAdapter da = new OdbcDataAdapter(query, con);

                da.Fill(ds);
                //cbLista.DataSource = ds.Tables[0];
                //cbLista.DisplayMember = ds.Tables[0].Columns[0].Caption;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return ds;
        }

        public string konfigurator(string parametr)
        {
            String value = "";
            XmlTextReader xtr = new XmlTextReader("konfigurator.xml");
            while (xtr.Read())
            {
                if (xtr.MoveToContent() == XmlNodeType.Element && xtr.Name == parametr)
                {
                    value = xtr.ReadString();
                }
            }
            return value;
        }

        public String otwieracz(String zacznij)
        {
            String plik = "";
            OpenFileDialog fDialog = new OpenFileDialog();            
            fDialog.Title = "Otwórz plik csv";
            fDialog.Filter = "pliki CSV|*.csv";
            fDialog.InitialDirectory = zacznij;
            if (fDialog.ShowDialog() == DialogResult.OK)
            {
                plik = fDialog.FileName.ToString();
            }
            fDialog.AddExtension = true;
            fDialog.CheckFileExists = true;
            fDialog.CheckPathExists = true;
            fDialog.InitialDirectory = @"C:\Windows";
            fDialog.ShowHelp = true;
            return plik;
        }

        public String zamykacz(String zacznij)
        {
            String plik = "";
            SaveFileDialog sDialog = new SaveFileDialog();
            sDialog.Title = "Zapisz plik csv";
            sDialog.Filter = "pliki CSV|*.csv";
            sDialog.InitialDirectory = zacznij;
            if (sDialog.ShowDialog() == DialogResult.OK)
            {
                plik = sDialog.FileName.ToString();
            }
            sDialog.AddExtension = true;
            sDialog.CheckFileExists = true;
            sDialog.CheckPathExists = true;
            sDialog.InitialDirectory = @"C:\Windows";
            sDialog.ShowHelp = true;
            return plik;
        }

        public void logToFile(string[] param)
        {
            string value = "";
            FileStream fs = new FileStream(@"c:\kan\logPistolety.txt", FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);
            foreach (string par in param)
            {
                value += par + ";";
            }
            sw.WriteLine(value);
            sw.Close();
        }

        public void logToBase(string ean,string document,string magazynier,string userid, string data,OdbcConnection con)
        {
            try
            {
                string query = "INSERT INTO COLLECTOR_LOG (EAN, DOCUMENT, MAGAZYNIER, USERID, DATA) VALUES ('"+ean+"','"+document+"', '"+magazynier+"', '"+userid+"', '"+data+"')";
                OdbcCommand comm = new OdbcCommand(query, con);
                comm.ExecuteNonQuery();
            }
            catch (Exception)
            {

            }
        }

        public void scanner(string ean,string document,OdbcConnection con)
        {
            string indeks = ean2index(ean, con);
            if ( indeks != "")
            {
                if (getCollectorValue(indeks, document, con) != null)
                {

                }

            }
            else
            {
                MessageBox.Show("Nieznany EAN");
            }
        }

        public string ean2index(string ean,OdbcConnection con)
        {
            string indeks="";
            string query = "SELECT b.INDEKS FROM BARCODE b WHERE b.BARCODE='"+ean+"'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if(dr.Read())
            {
                indeks = dr[0].ToString();
            }
            return indeks;
        }

        public string[] getCollectorValue(string indeks,string document,OdbcConnection con)
        {
            string[] value = null;
            string query = "SELECT p.DOCUMENT_ID, p.POSITION_ID, p.INDEKS, p.QUANTITY FROM COLLECTOR_POSITIONS p JOIN COLLECTOR_DOCUMENTS d on p.DOCUMENT_ID=d.DOCUMENT_ID where d.WZ_PROMAGG='"+document+"' and p.INDEKS='"+indeks+"'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                value = new string[4];
                value[0] = dr[0].ToString();
                value[1] = dr[1].ToString();
                value[2] = dr[2].ToString();
                value[3] = dr[3].ToString();
            }
            return value;
        }

        public string[] getKasjerValue(string indeks, string document, OdbcConnection con)
        {
            string[] value = null;
            string query = "SELECT p.DOCUMENT_ID, p.POSITION_ID, p.INDEKS, p.QUANTITY FROM KASJER_POSITIONS p JOIN KASJER_DOCUMENTS d on p.DOCUMENT_ID=d.DOCUMENT_ID where d.WZ_PROMAGG='" + document + "' and p.INDEKS='" + indeks + "'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                value = new string[4];
                value[0] = dr[0].ToString();
                value[1] = dr[1].ToString();
                value[2] = dr[2].ToString();
                value[3] = dr[3].ToString();
            }
            return value;

        }

        public void locationArch(string indeks, string indeksold, string location, OdbcConnection con)
        {
            try
            {
                string query = "INSERT INTO LOCATIONARCH (LOCATIONID,INDEKS,INDEKSOLD,DATA) VALUES ('" + location + "','" + indeks + "','" + indeksold + "','" + DateTime.Now + "')";
                OdbcCommand comm = new OdbcCommand(query, con);
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public string promagName2id(string promagName, OdbcConnection con)
        {
            string value = "";
            string query = "SELECT d.DOCUMENT_ID FROM COLLECTOR_DOCUMENTS d where d.WZ_PROMAGG='" + promagName + "'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                value = dr[0].ToString();
            }
            return value;
        }

        public void barcodeIndexGenerator(String query, String file, OdbcConnection con, OdbcConnection conZam)
        {
            try
            {
                OdbcCommand Qry = new OdbcCommand(query, con);
                OdbcDataReader myReader = Qry.ExecuteReader();
                StreamWriter sw = new StreamWriter(file);                
                while (myReader.Read())
                {
                    String sign = "B";
                    String barcodePrefix = "5901100";
                    String nazwa = myReader[0].ToString();
                    String nrKatalogowy = myReader[1].ToString();
                    int sezonId = Convert.ToInt32(myReader[2]);
                    String asortyment = myReader[3].ToString();
                    String kolor = myReader[4].ToString();
                    String producent = myReader[5].ToString();
                    String kraj = myReader[6].ToString();
                    String rozmiar = myReader[7].ToString();
                    String barcode = myReader[8].ToString();
                    String okno = myReader[9].ToString();
                    String ItemId = myReader[10].ToString();
                    String[] barTab = new String[7];
                    string qty1 = myReader[11].ToString();
                    string qty2 = myReader[12].ToString();
                    string qty3 = myReader[13].ToString();
                    string qty4 = myReader[14].ToString();
                    string qty5 = myReader[15].ToString();
                    string qty6 = myReader[16].ToString();
                    string qty7 = myReader[17].ToString();

                    if (barcode.Length == 35)
                    {
                        barTab[0] = barcode.Substring(0, 5);
                        barTab[1] = barcode.Substring(5, 5);
                        barTab[2] = barcode.Substring(10, 5);
                        barTab[3] = barcode.Substring(15, 5);
                        barTab[4] = barcode.Substring(20, 5);
                        barTab[5] = barcode.Substring(25, 5);
                        barTab[6] = barcode.Substring(30, 5);
                    }

                    String[] qty = { qty1, qty2, qty3, qty4, qty5, qty6, qty7 };
                    String line1 = "";
                    String[] rozTab = rozmiar.Split(';');

                    int i = 0;

                    if (barTab.Length == 7 && nrKatalogowy.Length != 0 && kolor.Length != 0)
                    {
                        String rozX = "";
                        foreach (string roz in rozTab)
                        {
                            if (roz.Length == 1)
                                rozX = "00" + roz;
                            if (roz.Length == 2)
                                rozX = "0" + roz;
                            //if (roz.Length > 3)
                            //    MessageBox.Show("Błąd rozmiaru w modelu :" + nazwa + "-" + nrKatalogowy);

                            if (nrKatalogowy.Length == 1)
                                nrKatalogowy = "00" + nrKatalogowy;
                            if (nrKatalogowy.Length == 2)
                                nrKatalogowy = "0" + nrKatalogowy;

                            if (kolor.Length == 1)
                                kolor = "00" + kolor;
                            if (kolor.Length == 2)
                                kolor = "0" + kolor;
                            //if(asortyment!="")
                            //    asortyment = term.id2asortyment(asortyment,lang,con);

                            if (qty[i] != "0" && qty[i] != null && barTab[i] != null && barTab[i] != "00000")
                            {                                
                                line1 = id2sezonKod(sezonId, con) + "." + nrKatalogowy + sign + "-" + kolor + "-" + rozX + ";" + licz(barcodePrefix + barTab[i]) ;                             
                                sw.Write(line1 + "\n");
                                AddBarcode(id2sezonKod(sezonId, con) + "." + nrKatalogowy + sign + "-" + kolor + "-" + rozX, licz(barcodePrefix + barTab[i]), conZam);
                            }
                            i++;
                        }
                    }
                }
                sw.Close();
                MessageBox.Show("Raport został wygenerowany");
            }
            catch (Exception e)
            {
                MessageBox.Show("Błąd w raporcie\n\n" + e.ToString());
            }
        }

        public String id2sezonKod(int sezonId, OdbcConnection con)
        {
            String rok = "";
            String sezon = "";
            for (int i = 0; i < 20; i++)
            {
                float wynik = (sezonId - i) / 65536;
                if (wynik % 10 == 0)
                {
                    rok = (wynik - 2000).ToString();
                    if (i.ToString().Length == 1)
                        sezon = "0" + i.ToString();
                    if (i.ToString().Length == 2)
                        sezon = i.ToString();
                }
            }
            return "T" + rok + sezon;
        }

        public String licz(String EAN)
        {
            String wynik = "";
            int[] wagi = { 1, 3, 1, 3, 1, 3, 1, 3, 1, 3, 1, 3 };
            int value = 0;
            if (EAN.Length == 12)
            {
                for (int i = 0; i < 12; i++)
                {
                    value += wagi[i] * Convert.ToInt32(EAN.Substring(i, 1));
                    wynik += EAN.Substring(i, 1);
                }
                value = 10 - value % 10;
                if (value == 10) value = 0;
                return wynik + value.ToString();
            }
            else
            {
                return "0000000000000";
            }
        }

        public void AddBarcode(string indeks, string barcode, OdbcConnection con)
        {
            string query = "SELECT b.BARCODE, b.INDEKS FROM BARCODE b WHERE b.INDEKS='"+indeks+"' and b.BARCODE='"+barcode+"'";
            //MessageBox.Show(query);
            try
            {
                OdbcCommand comm = new OdbcCommand(query, con);
                OdbcDataReader dr = comm.ExecuteReader();
                if (!dr.Read())
                {                    
                    string queryI = "INSERT INTO BARCODE (BARCODE, INDEKS) VALUES ('"+barcode+"','"+indeks+"')";                    
                    OdbcCommand commI = new OdbcCommand(queryI, con);
                    commI.ExecuteNonQuery();
                }
            }
            catch (Exception e) 
            {
                MessageBox.Show(e.ToString());
            }
            //barcodeIndexGenerator(query, fileName, conTerm);
        }

        public string fixStr(string tekst, int toStr, string znak)
        {
            if (znak.Length != 1)
                return "";
            if (tekst.Length > toStr)
                return "";
            for (int i = tekst.Length; i < toStr; i++)
            {
                tekst = znak + tekst;
            }

            return tekst;
        }

        public string EAN2Indeks(string EAN,OdbcConnection con)
        {
            string value="";
            string query = "SELECT b.INDEKS FROM BARCODE b where b.BARCODE='"+EAN+"'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();

            if(dr.Read())
            {
                value = dr[0].ToString();
            }

            return value;
        }

        public int checkDocument(string indeks, string dokument, OdbcConnection con)
        {
            int  value = 0;
            string query = "SELECT a.QUANTITY FROM COLLECTOR_POSITIONS a where a.DOCUMENT_ID ='"+dokument+"' and a.INDEKS='"+indeks+"'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                value = Convert.ToInt32(dr[0]);
            }
            return value;
        }

        public int getQtyKasjer(string indeks, string dokument, OdbcConnection con)
        {
            int value = 0;
            string query = "SELECT a.QUANTITY FROM KASJER_POSITIONS a where a.DOCUMENT_ID ='" + dokument + "' and a.INDEKS='" + indeks + "'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                value = Convert.ToInt32(dr[0]);
            }
            return value;
        }

        public int getQtyCollector(string indeks, string dokument, OdbcConnection con)
        {
            int value = 0;
            string query = "SELECT a.QUANTITY FROM KASJER_POSITIONS a where a.DOCUMENT_ID ='" + dokument + "' and a.INDEKS='" + indeks + "'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                value = Convert.ToInt32(dr[0]);
            }
            return value;
        }

        public void changeKasjerPositions(string indeks, string dokument, int qty, OdbcConnection con)
        {
            try
            {
                string query = "UPDATE KASJER_POSITIONS SET quantity=" + qty + " WHERE a.DOCUMENT_ID='"+dokument+"' and INDEKS='"+indeks+"'";
                OdbcCommand comm = new OdbcCommand(query, con);
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message+"\n\n"+"indeks: "+indeks+"\ndokument: "+dokument+"\nqty: "+qty.ToString());
            }
        }

        public void setKasjerDocument(string[] dane, OdbcConnection con)
        {
            if (!isKasjerDocument(dane[0], con))
            {
                string query = "INSERT INTO KASJER_DOCUMENTS (DOCUMENT_ID, WZ_PROMAGG, SHOP_ID, CREATE_DATE, WAREHOUSE) VALUES ('" + dane[0] + "', '" + dane[1] + "','" + dane[2] + "', '" + DateTime.Today.ToString() + "','" + dane[4] + "')";
                OdbcCommand comm = new OdbcCommand(query, con);
                comm.ExecuteNonQuery();
            }
            else
            {
                MessageBox.Show("Dokument: " + dane[0] + " już istnieje");
            }
        }

        public string[] getCollectorDocument(string dokument,OdbcConnection con)
        {
            string[] value = new string[6];
            string query = "SELECT DOCUMENT_ID, WZ_PROMAGG, SHOP_ID, CREATE_DATE, WAREHOUSE, DOCUMENT_TYPE FROM COLLECTOR_DOCUMENTS where WZ_PROMAGG='"+dokument+"'";
            
            OdbcCommand comm = new OdbcCommand(query,con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                value[0] = dr[0].ToString();
                value[1] = dr[1].ToString();
                value[2] = dr[2].ToString();
                value[3] = dr[3].ToString();
                value[4] = dr[4].ToString();
                value[5] = dr[5].ToString();
            }
            
            return value;
        }


        public string[] getKasjerDocument(string dokument, OdbcConnection con)
        {
            string[] value = new string[5];
            string query = "SELECT kd.DOCUMENT_ID, kd.WZ_PROMAGG, kd.SHOP_ID, kd.CREATE_DATE, kd.WAREHOUSE FROM KASJER_DOCUMENTS kd where kd.WZ_PROMAGG='" + dokument + "'";

            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                value[0] = dr[0].ToString();
                value[1] = dr[1].ToString();
                value[2] = dr[2].ToString();
                value[3] = dr[3].ToString();
                value[4] = dr[4].ToString();
                
            }

            return value;
        }

        public void blockKasjerDocument(string dokument, string command, OdbcConnection con)
        {
            string query="";
            if (command=="closed")
            {
                query = "UPDATE KASJER_DOCUMENTS SET CLOSED='t' WHERE WZ_PROMAGG='"+dokument+"'";
            }
            else if (command=="use")
            {
                query = "UPDATE KASJER_DOCUMENTS SET USE='t' WHERE WZ_PROMAGG='" + dokument + "'";
            }
            else if (command == "clear")
            {
                query = "UPDATE KASJER_DOCUMENTS SET USE='n' WHERE WZ_PROMAGG='" + dokument + "'";
            }
            else if (command == "open")
            {
                query = "UPDATE KASJER_DOCUMENTS SET CLOSED='n' WHERE WZ_PROMAGG='" + dokument + "'";
            }

            try
            {
                OdbcCommand com = new OdbcCommand(query, con);
                com.ExecuteNonQuery();
                
                //MessageBox.Show(query);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        public void scanning(string indeks, string dokument, OdbcConnection con)
        {
            string[] tab = getCollectorDocument(dokument, con);            
            if (!isKasjerDocument(dokument, con))
            {
                string[] tabK = getKasjerDocument(dokument, con);
                if (tabK[0] == null)
                {
                    insertKasjerDocument(tab, con);
                    //MessageBox.Show("był insert");
                }
                //MessageBox.Show("powinien być insert");
            }

            blockKasjerDocument(dokument, "use", con);//blokowanie dokumentu do edycji
            int collectorQty = getCollectorPositionsQty(indeks,dokument,con);
            int kasjerQty    = getKasjerPositionsQty(indeks,dokument,con);

            if (kasjerQty < collectorQty && collectorQty != 0)
            {
                int qty = kasjerCounter(dokument, con) + 1;
                string query = "";
                if (kasjerQty == 0)
                {
                    query = "INSERT INTO KASJER_POSITIONS (DOCUMENT_ID, POSITION_ID, INDEKS, QUANTITY) VALUES ('" + dokument + "', " + qty.ToString() + ", '" + indeks + "', 1)";
                }
                if (kasjerQty > 0)
                {
                    int qtyUpdate=kasjerQty+1;
                    query = "UPDATE KASJER_POSITIONS SET QUANTITY='"+qtyUpdate.ToString()+"' WHERE DOCUMENT_ID='"+dokument+"'";
                }
                OdbcCommand com = new OdbcCommand(query, con);
                com.ExecuteNonQuery();
            }
            else
            {
                string komunikat = "";
                if (kasjerQty >= collectorQty)
                {
                    komunikat = "Przekroczona ilość";
                }
                if (collectorQty == 0)
                {
                    komunikat = "Towar spoza listy";
                }                
                MessageBox.Show(komunikat, "Błędny towar", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        public int kasjerCounter(string dokument,OdbcConnection con)
        {
            int value=0;
            string query = "SELECT COUNT(*) FROM KASJER_POSITIONS k where k.DOCUMENT_ID='"+dokument+"'";
            OdbcCommand com = new OdbcCommand(query,con);
            OdbcDataReader dr = com.ExecuteReader();
            if (dr.Read())
                value = Convert.ToInt32(dr[0]);
            return value;
        }


        public void insertKasjerDocument(string[] tab,OdbcConnection con) //tworzy wiersz w kasjer_documents na podstawie collector_documents
        {
            string query="INSERT INTO KASJER_DOCUMENTS (DOCUMENT_ID, WZ_PROMAGG, SHOP_ID, CREATE_DATE, WAREHOUSE,USE,CLOSED) VALUES ("+tab[0]+", "+tab[1]+", '"+tab[2]+"', '"+DateTime.Now+"', '"+tab[4]+"','n','n')";
            MessageBox.Show(query);
            OdbcCommand com = new OdbcCommand(query, con);
            com.ExecuteNonQuery();
        }

        public bool isKasjerDocument(string document, OdbcConnection con) // metoda sprawdza czy istnieje dokument w tabeli KASJER_DOCUMENTS
        {
            bool value = false;
            string query = "SELECT a.DOCUMENT_ID FROM KASJER_DOCUMENTS a WHERE a.DOCUMENT_ID='"+document+"'";
            OdbcCommand comm  = new OdbcCommand(query,con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
                value = true;            
            return value;
        }

        public int getKasjerPositionsQty(string indeks,string dokument, OdbcConnection con) // metoda zwraca ilość sztuk indeksu dla wskazanego dokumentu w tabeli KASJER_POSITIONS
        {
            int value = 0;
            string query = "SELECT kp.QUANTITY FROM KASJER_POSITIONS kp WHERE kp.DOCUMENT_ID="+dokument+" AND kp.INDEKS='"+indeks+"'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
                value = Convert.ToInt32(dr[0]);

            return value;
        }

        public int getCollectorPositionsQty(string indeks, string dokument, OdbcConnection con) // metoda zwraca ilość sztuk indeksu dla wskazanego dokumentu w tabeli COLLECTOR_POSITIONS
        {
            int value = 0;
            string query = "SELECT kp.QUANTITY FROM COLLECTOR_POSITIONS kp WHERE kp.DOCUMENT_ID=" + dokument + " AND kp.INDEKS='" + indeks + "'";
            OdbcCommand comm = new OdbcCommand(query, con);
            OdbcDataReader dr = comm.ExecuteReader();
            if (dr.Read())
                value = Convert.ToInt32(dr[0]);

            return value;
        }

        public void exportBarcode(string file,OdbcConnection con)
        {
            try
            {
                string query = "SELECT b.BARCODE, b.INDEKS FROM BARCODE b";
                OdbcCommand comm = new OdbcCommand(query, con);
                OdbcDataReader dr = comm.ExecuteReader();
                StreamWriter sw = new StreamWriter(file);
                while (dr.Read())
                {
                    sw.WriteLine(dr[0].ToString() + ";" + dr[1].ToString());
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void writeBarcode2SQL(OdbcConnection con,SQLiteConnection conSL)
        {
            try
            {
                string querySelect = "select barcode,indeks from barcode";
                OdbcCommand com = new OdbcCommand(querySelect, con);
                OdbcDataReader dr = com.ExecuteReader();
                
                SQLiteCommand comSL1 = new SQLiteCommand("delete from barcode", conSL);
                comSL1.ExecuteNonQuery();

                int i = 0;
                while (dr.Read())
                {
                    try
                    {
                        i++;
                        write2SQLite(dr[0].ToString(), dr[1].ToString(),conSL);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
                MessageBox.Show(i.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void write2SQLite(string barcode, string indeks,SQLiteConnection conSL)
        {
            string query = "INSERT INTO BARCODE (BARCODE, INDEKS) VALUES ('" + barcode + "', '" + indeks + "')";
            SQLiteCommand comS = new SQLiteCommand(query, conSL);
            comS.ExecuteNonQuery();
        }

        public int countLocation(OdbcConnection con)//liczy ilość zajętych lokalizacji
        {
            int value = 0;
            try
            {
                string query = "select count(*) from location";
                OdbcCommand com = new OdbcCommand(query, con);
                OdbcDataReader dr = com.ExecuteReader();
                if (dr.Read())
                {
                    value = Convert.ToInt32(dr[0].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return value;
        }

        public int countBarcode(OdbcConnection con)
        {
            int value = 0;
            try
            {
                string query = "SELECT count(distinct(a.BARCODE)) FROM BARCODE a";
                OdbcCommand com = new OdbcCommand(query, con);
                OdbcDataReader dr = com.ExecuteReader();
                if (dr.Read())
                {
                    value = Convert.ToInt32(dr[0].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return value;
        }//liczy ilość barcode'ów 

        public int countIndeks(OdbcConnection con)
        {
            int value = 0;
            string query = "SELECT count(distinct(a.INDEKS)) FROM BARCODE a";
            OdbcCommand com = new OdbcCommand(query, con);
            OdbcDataReader dr = com.ExecuteReader();
            if (dr.Read())
            {
                value = Convert.ToInt32(dr[0].ToString());
            }
            return value;
        }//liczy ilość indeksów

        public string countOpenDocument(OdbcConnection con)
        {
            string value = "";
            try
            {
                string query = "SELECT COUNT(cd.WZ_PROMAGG) FROM COLLECTOR_DOCUMENTS cd";
                OdbcCommand com = new OdbcCommand(query, con);
                OdbcDataReader dr = com.ExecuteReader();
                if (dr.Read())
                    value = dr[0].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return value;
        }

        public string wz2documentid(string wz,OdbcConnection con)
        {
            string value = "";
            string query = "SELECT cd.DOCUMENT_ID FROM COLLECTOR_DOCUMENTS cd WHERE cd.WZ_PROMAGG='"+wz+"'";
            OdbcCommand com = new OdbcCommand(query, con);
            OdbcDataReader dr = com.ExecuteReader();
            if (dr.Read())
            {
                value = dr[0].ToString();
            }
            return value;
        }


        public void fillMagazynierzy(DataGridView dgv,string imie, string nazwisko,OdbcConnection con)
        {
            try
            {
                DataSet ds = new DataSet();
                string query = "SELECT m.MAGAZYNIERID, m.MAGAZYNIERNAME, m.ACTIVITY FROM MAGAZYNIERZY m where m.magazyniername like '%" + imie + "%" + nazwisko + "%'";
                OdbcDataAdapter da = new OdbcDataAdapter(query, con);
                da.Fill(ds);
                dgv.DataSource = ds.Tables[0];
                dgv.Columns[0].Width = 100;
                dgv.Columns[1].Width = 178;
                dgv.Columns[2].Width = 30;

                dgv.Columns[0].HeaderText = "login";
                dgv.Columns[1].HeaderText = "imię i nazwisko";
                dgv.Columns[2].HeaderText = "aktywny?";

                dgv.Columns[0].ReadOnly = true;
                dgv.Columns[1].ReadOnly = true;
                dgv.Columns[2].ReadOnly = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }


        public void insertMagazynier(string imie,string nazwisko,OdbcConnection con)
        {
            // str3 is a lower case copy of str2, using English-United States culture.
            Console.WriteLine("str3 = Lower case copy of str2 using English-United States culture.");
            imie = imie.ToLower(new CultureInfo("pl", true));

            string name = nameMaker(imie) + " " + nameMaker(nazwisko);
            string login = antyPolski(imie.Substring(0, 1).ToLower() + nazwisko.ToLower());
            string query = "INSERT INTO MAGAZYNIERZY (MAGAZYNIERID, MAGAZYNIERNAME, ACTIVITY, DATA) VALUES ('"+cutter(login,20)+"', '"+cutter(name,25)+"', 'T', '"+DateTime.Now+"')";
            MessageBox.Show(query);
            OdbcCommand com = new OdbcCommand(query, con);
            com.ExecuteNonQuery();
        }

        public string nameMaker(string name)
        {
            return name.Substring(0, 1).ToUpper() + name.Substring(1, name.Length - 1).ToLower();
        }

        public string antyPolski(string name)
        {
            return name.ToLower().Replace('ą', 'a').Replace('ć', 'c').Replace('ę', 'e').Replace('ł', 'l').Replace('ń', 'n').Replace('ó', 'o').Replace('ź', 'z').Replace('ż', 'z').Replace('ś', 's').Replace('-','_');
        }

        public string cutter(string name,int x)
        {           
            string value = name;
            if (name.Length > 0 && name.Length>x)
                value=name.Substring(0,x);
            return value;
        }

    }
}

